package jgespres;

import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.beans.PropertyChangeListener;
import java.beans.PropertyChangeEvent;
import java.util.logging.*;

/**
 * Création/Ouverture d'un base de données
 * 
 * @author guerfyld
 * @version 2.0
 */
public class JBaseDlg extends JDialog {
    // Elements à gerer
    private JTextField driverName;
    private JTextField urlConnect;
    private boolean isBaseChanged = false;
    private Connection connect;

    // Requêtes de création de base
    public static final String CRTSTD = "CREATE TABLE Students (id INT not null, libS CHAR(10), libL CHAR(50), nord SMALLINT, groupe CHAR(10), extra CHAR(10))";
    public static final String CRTTHM = "CREATE TABLE Themes (id INT not null, date DATE, libS CHAR(10), libL CHAR(50), libXL CHAR(100))";
    public static final String CRTPLN = "CREATE TABLE Planning2 (date DATE not null, idStd INT, extraStd CHAR(10), session SMALLINT, idThm INT, idHlp INT, extraHlp CHAR(10), status INT, separator BOOLEAN, extra CHAR(10))";
    public static final String CRTCRF = "CREATE TABLE CrossRef (idStd1 INT not null, idStd2 INT not null, cnt INT not null)";
    public static final String CRTTBL = "CREATE TABLE Table2 (idTable CHAR(15), keyTable CHAR(15), text CHAR(50))";
    public static final String CRTARC = "CREATE TABLE Archive (date DATE, libS CHAR(10), libL CHAR(50), libXL CHAR(100), session SMALLINT, idStd INT, extraStd CHAR(10), idHlp INT, extraHlp CHAR(10), extra CHAR(10), status INT not null)";

    // Logger
    private static final Logger logger = Logger.getLogger("jgespres");

    /**
     * Constructeur d'objets de classe JBaseDlg
     */
    public JBaseDlg() {
        // Appel constructeur (Boite de dialogue Modal)
        super(JFrame.getFrames()[0], jgespres.getResourceString("Planning.Base"), true);
        
        // Initialisation objet
        createGUI();
        
        // Affichage boite de dialogue
        setVisible(true);
    }
    
    /**
     * Récupère driver JDBC
     * 
     * @return      Driver JDBC
     */
    public final String getDriver() {
        return driverName.getText().trim();
    }
    
    /**
     * Récupère url JDBC
     * 
     * @return      Url JDBC
     */
    public final String getUrl() {
        return urlConnect.getText().trim();
    }

    /**
     * Création élèments de l'affichage
     */
    private void createGUI() {
        // Libellé + Zone saisie driver JDBC
        JPanel driverPane = new JPanel();
        driverPane.setLayout(new BoxLayout(driverPane, BoxLayout.LINE_AXIS));
        JLabel label = new JLabel(jgespres.getResourceString("Planning.Base.Driver"));
        driverPane.add(label);
        driverPane.add(Box.createRigidArea(new Dimension(0,5)));
        driverName = new JTextField(jgespres.getResourceString("Planning.Base.DriverDefault"), 30);
        driverPane.add(driverName);
        driverPane.setBorder(BorderFactory.createEmptyBorder(10,10,10,10));
        
        // Libellé et Zone saisie URL JDBC
        JPanel urlPane = new JPanel();
        urlPane.setLayout(new BoxLayout(urlPane, BoxLayout.LINE_AXIS));
        label = new JLabel(jgespres.getResourceString("Planning.Base.Url"));
        urlPane.add(label);
        urlPane.add(Box.createRigidArea(new Dimension(0,5)));
        urlConnect = new JTextField(jgespres.getResourceString("Planning.Base.UrlDefault"), 30);
        urlPane.add(urlConnect);
        urlPane.setBorder(BorderFactory.createEmptyBorder(10,10,10,10));
        
        // Boutons d'ouverture de la base
        JPanel buttonPane = new JPanel();
        buttonPane.setLayout(new BoxLayout(buttonPane, BoxLayout.LINE_AXIS));
        buttonPane.setBorder(BorderFactory.createEmptyBorder(0, 10, 10, 10));
        buttonPane.add(Box.createHorizontalGlue());
        JButton button = new JButton(jgespres.getResourceString("Dialog.Ok"));
        button.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent ae) {
                try {
                    openBase();
                }
                catch (Exception e) {
                    System.out.println("Erreur:"+e);
                }
            }
        });
        buttonPane.add(button);
        
        // Définition de la boite de dialogue
        setResizable(false);
        getContentPane().setLayout( new BorderLayout() );
        getContentPane().add(driverPane, BorderLayout.PAGE_START);
        getContentPane().add(urlPane, BorderLayout.CENTER);
        getContentPane().add(buttonPane, BorderLayout.PAGE_END);
        pack();
        jgespres.centerDialog(this);
    }
    
    /**
     * Demande d'ouverture d'une base de données
     */
    private void openBase() throws Exception {
        // Tentative chargement driver
        Class.forName( driverName.getText() );
        
        // Tentative ouverture de la base de données
        try {
            connect = java.sql.DriverManager.getConnection( urlConnect.getText(), "","");
        }
        catch (Exception e) {
            // Préparation boite de dialogue "Creation Base ?"
            final JOptionPane optionPane = new JOptionPane(
                jgespres.getResourceString("Planning.Base.CreateMsg"),
                JOptionPane.QUESTION_MESSAGE,
                JOptionPane.YES_NO_OPTION);
            final JDialog dialog = new JDialog(JFrame.getFrames()[0], jgespres.getResourceString("Planning.Base.CreateTitle"), true);
            dialog.setDefaultCloseOperation(JDialog.DO_NOTHING_ON_CLOSE);
            optionPane.addPropertyChangeListener(new PropertyChangeListener() {
                public void propertyChange(PropertyChangeEvent e) {
                    String prop = e.getPropertyName();
                    if (dialog.isVisible() && (e.getSource() == optionPane) && (prop.equals(JOptionPane.VALUE_PROPERTY)))
                        dialog.setVisible(false);
                }
            });
            dialog.setContentPane(optionPane);
            dialog.pack();
            jgespres.centerDialog(dialog);
            // Affichage boite de dialogue
            dialog.setVisible(true);
            // Traitement de la réponse
            int value = ((Integer)optionPane.getValue()).intValue();
            dialog.dispose();
            if (value == JOptionPane.YES_OPTION) {
                if (!createBase()) 
                    return;
            }
            else
                return;
        }
        isBaseChanged = true;
        
        // Retourne à l'écran principal
        setVisible(false);
    }

    /**
     * Demande d'ouverture d'une base de données
     * 
     * @return      Traitement effectué avec succés (Vrai/Faux)
     */
    private boolean createBase() {
        try {
            logger.log(Level.INFO, "Création Base...");
            
            // Creation de la Base de données
            logger.log(Level.INFO, urlConnect.getText()+"?create=true");
            connect = DriverManager.getConnection( urlConnect.getText()+"?create=true", "","");
            
            // Création des divers fichiers
            Statement stmt = connect.createStatement();
            stmt.executeUpdate(CRTSTD);
            stmt.executeUpdate(CRTTHM);
            stmt.executeUpdate(CRTPLN);
            stmt.executeUpdate(CRTCRF);
            stmt.executeUpdate(CRTTBL);
            stmt.executeUpdate(CRTARC);
            
            return true;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        
        return false;
    }
    
    /**
     * Renvoi si une nouvelle connection a été établie
     * 
     * @return      Vrai/Faux
     */
    public boolean baseChanged() {
        return isBaseChanged;
    }
    
    /**
     * Renvoi nouvelle connection établie
     * 
     * @return      Nouvelle connection
     */
    public Connection getConnection() {
        return connect;
    }
    
    /**
     * Vérification Base de données
     */
    public static void checkBase(Connection connect) {
        try {
            DatabaseMetaData md = connect.getMetaData();
            // Convertion Base v1 -> v2
            ResultSet rs = md.getTables(null, null, "Table2", null);
            if (!rs.next()) 
                DBTools.convertDBv1to2(connect);
        }
        catch (Exception e) {
            System.out.println("ERREUR !!");
            e.printStackTrace();
        }
    }
}
